package com.xtm.exercise.utils.export;

import jxl.SheetSettings;
import jxl.Workbook;
import jxl.format.*;
import jxl.write.*;
import jxl.write.biff.RowsExceededException;
import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.web.client.RestClientException;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.awt.*;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.net.URLDecoder;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

//import org.apache.poi.ss.usermodel.VerticalAlignment;


/**
 *
 * 功能描述
 *
 * @author createtime : 2013-11-11 下午5:00:45
 * @see
 * @since
 */
public class ExcelUtil {
	/**
	 * 日志
	 */
	private static final Log log = LogFactory.getLog(ExcelUtil.class);

	/**
	 * 构造函数
	 */
	public ExcelUtil() {
	}

	/**
	 * 导出excel
	 *
	 * @param p_request
	 *            HttpServletRequest
	 * @param p_response
	 *            HttpServletResponse
	 * @param p_fileName
	 *            文件名称
	 * @param p_Titles
	 *            字段名
	 * @param p_listContent
	 *            p_listContent
	 * @since
	 */
	@SuppressWarnings("rawtypes")
	public static final void exportExcel(HttpServletRequest p_request,
										 HttpServletResponse p_response, String p_fileName,
										 String[] p_Titles, String[] p_columns, List p_listContent) {
		try {
			OutputStream os = p_response.getOutputStream();
			p_response.reset();
			String filenamedisplay = URLEncoder.encode(p_fileName, "UTF-8");
			if ("FF".equals(getBrowser(p_request))) {
				filenamedisplay = new String(p_fileName.getBytes("UTF-8"),
						"iso-8859-1");
			}
			p_response.addHeader("Content-Disposition", (new StringBuilder())
					.append("attachment; filename=").append(filenamedisplay)
					.toString());
			p_response.addHeader("Content-Type", "application/vnd.ms-excel");
			WritableWorkbook workbook = Workbook.createWorkbook(os);
			WritableSheet sheet = workbook.createSheet("Sheet1", 0);
			SheetSettings sheetset = sheet.getSettings();
			sheetset.setProtected(false);
			WritableFont normalFont = new WritableFont(WritableFont.ARIAL, 11);
			WritableFont boldFont = new WritableFont(WritableFont.ARIAL, 14,
					WritableFont.BOLD);
			WritableCellFormat wcf_center = new WritableCellFormat(boldFont);
			wcf_center.setBorder(Border.ALL, BorderLineStyle.THIN);
			wcf_center.setVerticalAlignment(VerticalAlignment.CENTRE);
			wcf_center.setAlignment(Alignment.CENTRE);
			wcf_center.setWrap(false);
			// workbook.setColourRGB(Colour., i, j, k)
			Color co = Color.decode("#00B0F0");
			workbook.setColourRGB(Colour.ORANGE, co.getRed(), co.getGreen(),
					co.getBlue());
			wcf_center.setBackground(Colour.ORANGE);
			// wcf_center.setShrinkToFit(true);
			WritableCellFormat wcf_left = new WritableCellFormat(normalFont);
			wcf_left.setBorder(Border.NONE, BorderLineStyle.THIN);
			wcf_left.setVerticalAlignment(VerticalAlignment.CENTRE);
			wcf_left.setAlignment(Alignment.LEFT);
			wcf_left.setWrap(false);
			// 生成列头
			for (int i = 0; i < p_Titles.length; i++) {
				sheet.addCell(new Label(i, 0, p_Titles[i], wcf_center));
				// 设置每列宽度
				sheet.setColumnView(i, 35);
			}

			for (int row = 0; row < p_listContent.size(); row++) {
				Map<String, Object> dataRow = (Map) p_listContent.get(row);
				for (int col = 0; col < p_columns.length; col++) {
					if (p_columns[col] != null && !p_columns[col].equals("")) {
						for (Map.Entry<String, Object> entry : dataRow.entrySet()) {
							if(p_columns[col].equals(entry.getKey())) {
								String listvalue = entry.getValue().toString();
								Label label = new Label(col, row + 1, listvalue,
										wcf_left);
								sheet.addCell(label);
							}
						}
					} else {
						Label label = new Label(col, row + 1, "", wcf_left);
						sheet.addCell(label);
					}
				}

			}
			workbook.write();
			try {

				workbook.close();
			} catch (Exception e) {
				// TODO: handle exception
			}
			os.flush();
			os.close();
		} catch (Exception e) {
			String message = "\u751F\u6210 Excel \u65F6\u51FA\u9519";
			if (log.isErrorEnabled()) {
				log.error(
						(new StringBuilder()).append("MXFRAMEWORK")
								.append(message).toString(), e);
			}
		}
	}

	/**
	 * 导出excel
	 *
	 * @param
	 *
	 * @param
	 *
	 * @param p_fileName
	 *            文件名称
	 * @param p_Titles
	 *            字段名
	 * @param
	 *
	 * @since
	 */
	@SuppressWarnings("rawtypes")
	public static final <T> void exportExcel3(WritableWorkbook workbook ,String p_fileName , String[] p_Titles, List<T> listContent , Integer sheetInd , String sheetName) {
		synchronized (log) {
			try {
				/*OutputStream os = p_response.getOutputStream();
				p_response.reset();
				String filenamedisplay = URLEncoder.encode(p_fileName, "UTF-8");
				if ("FF".equals(getBrowser(p_request))) {
					filenamedisplay = new String(p_fileName.getBytes("UTF-8"),
							"iso-8859-1");
				}
				p_response.addHeader("Content-Disposition", (new StringBuilder())
						.append("attachment; filename=").append(filenamedisplay).append(".xls")
						.toString());
				p_response.addHeader("Content-Type", "application/vnd.ms-excel");
				WritableWorkbook workbook = Workbook.createWorkbook(os);*/
				WritableSheet sheet = workbook.createSheet(sheetName , sheetInd);
				SheetSettings sheetset = sheet.getSettings();
				sheetset.setProtected(false);
				WritableFont normalFont = new WritableFont(WritableFont.ARIAL, 11);
				WritableFont boldFont = new WritableFont(WritableFont.ARIAL, 14,
						WritableFont.BOLD);
				WritableCellFormat wcf_center = new WritableCellFormat(boldFont);
				wcf_center.setBorder(Border.ALL, BorderLineStyle.THIN);
				wcf_center.setVerticalAlignment(VerticalAlignment.CENTRE);
				wcf_center.setAlignment(Alignment.CENTRE);
				wcf_center.setWrap(false);
				// workbook.setColourRGB(Colour., i, j, k)
				Color co = Color.decode("#00B0F0");
				workbook.setColourRGB(Colour.ORANGE, co.getRed(), co.getGreen(),
						co.getBlue());
				wcf_center.setBackground(Colour.ORANGE);
				// wcf_center.setShrinkToFit(true);
				WritableCellFormat wcf_left = new WritableCellFormat(normalFont);
				wcf_left.setBorder(Border.NONE, BorderLineStyle.THIN);
				wcf_left.setVerticalAlignment(VerticalAlignment.CENTRE);
				wcf_left.setAlignment(Alignment.LEFT);
				wcf_left.setWrap(false);
				// 生成列头
				for (int i = 0; i < p_Titles.length; i++) {
					sheet.addCell(new Label(i, 0, p_Titles[i], wcf_center));
					// 设置每列宽度
					sheet.setColumnView(i, 35);
				}

				if(listContent != null && listContent.size() > 0){
					for (int row = 0; row < listContent.size(); row++) {
						T dataRow = listContent.get(row);
						Field[] fieldArr = dataRow.getClass().getDeclaredFields();
						Integer col = 0;
						for(Field f : fieldArr){
							Annotation annotation = f.getAnnotation(FieldForExport.class);
							if(!f.getName().toLowerCase().contains("id") || annotation != null){
								String getterName = "get" + f.getName().substring(0 , 1).toUpperCase() + f.getName().substring(1);
								Method method = dataRow.getClass().getDeclaredMethod(getterName , new Class[]{});
								Object getterObj = method.invoke(dataRow , new Object[]{});
								if(getterObj != null){
									Label label = new Label(col, row + 1 , getterObj.toString() ,
											wcf_left);
									sheet.addCell(label);
									col++;
								}
							}else{

							}
						}

					}
				}

				/*try {

					workbook.close();
				} catch (Exception e) {
					// TODO: handle exception
				}*/
				/*os.flush();
				os.close();*/
			} catch (Exception e) {
				String message = "\u751F\u6210 Excel \u65F6\u51FA\u9519";
				if (log.isErrorEnabled()) {
					log.error(
							(new StringBuilder()).append("MXFRAMEWORK")
									.append(message).toString(), e);
				}
			}/*finally{
				try {
					os.flush();
					os.close();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}*/
		}

	}


	/**
	 * 2015-4-21根据poi做导出excel的功能
	 *
	 * @author lichao fileName 导出名称 titles 导出的字段名称 contents 内容
	 */
	public static void exportExcel2(HttpServletRequest request,
									HttpServletResponse response, String fileName, String[] titles,
									List contents) {
		try{
			response.reset();
			String filenamedisplay = URLEncoder.encode(fileName, "UTF-8");
			if ("FF".equals(getBrowser(request))) {
				filenamedisplay = new String(fileName.getBytes("UTF-8"),
						"iso-8859-1");
			}
			response.addHeader("Content-Disposition", (new StringBuilder())
					.append("attachment; filename=").append(filenamedisplay)
					.toString());
			response.addHeader("Content-Type", "application/vnd.ms-excel");

			OutputStream out = response.getOutputStream();

			HSSFWorkbook wb = new HSSFWorkbook();
			HSSFSheet sheet1 = wb.createSheet("sheet1");
			// 遍历标题字段集合
			HSSFRow row = sheet1.createRow(0);
			for (int i = 0; i < titles.length; i++) {
				row.createCell(i).setCellValue(titles[i]!=null?titles[i].toString():"");
				sheet1.setColumnWidth(i, 3000);  //设置宽度
			}

			//遍历内容行数的集合
			for(int i=0;i<contents.size();i++){
				row=sheet1.createRow(i+1);
				Object[] content = (Object[]) contents.get(i);
				//遍历每条内容的字段集合
				for(int j=0;j<content.length;j++){
					row.createCell(j).setCellValue(content[j]!=null?content[j].toString():""); //存入excel中
				}
			}


			wb.write(out);
			out.flush();
			out.close();
		}catch(Exception e){
			log.error("导出车辆基本信息出现异常");
		}

	}


	/**
	 * 导出excel
	 *
	 * @param p_request
	 *            HttpServletRequest
	 * @param p_response
	 *            HttpServletResponse
	 * @param p_fileName
	 *            文件名称
	 * @param p_Titles
	 *            字段名
	 * @param p_listContent
	 *            p_listContent
	 * @since
	 */
	@SuppressWarnings("rawtypes")
	public static final void exportExcelMulti(HttpServletRequest p_request,
											  HttpServletResponse p_response, String p_fileName,
											  int p_rows, List p_Titles, List p_listContent) {
		try {
			OutputStream os = p_response.getOutputStream();
			p_response.reset();
			String filenamedisplay = URLEncoder.encode(p_fileName, "UTF-8");
			if ("FF".equals(getBrowser(p_request))) {
				filenamedisplay = new String(p_fileName.getBytes("UTF-8"),
						"iso-8859-1");
			}
			p_response.addHeader("Content-Disposition", (new StringBuilder())
					.append("attachment; filename=").append(filenamedisplay)
					.toString());
			p_response.addHeader("Content-Type", "application/vnd.ms-excel");
			WritableWorkbook workbook = Workbook.createWorkbook(os);
			WritableSheet sheet = workbook.createSheet("Sheet1", 0);
			SheetSettings sheetset = sheet.getSettings();
			sheetset.setProtected(false);
			WritableFont normalFont = new WritableFont(WritableFont.ARIAL, 11);
			WritableFont boldFont = new WritableFont(WritableFont.ARIAL, 14,
					WritableFont.BOLD);
			WritableCellFormat wcf_center = new WritableCellFormat(boldFont);
			wcf_center.setBorder(Border.ALL, BorderLineStyle.THIN);
			wcf_center.setVerticalAlignment(VerticalAlignment.CENTRE);
			wcf_center.setAlignment(Alignment.CENTRE);
			wcf_center.setWrap(false);
			// workbook.setColourRGB(Colour., i, j, k)
			Color co = Color.decode("#00B0F0");
			workbook.setColourRGB(Colour.ORANGE, co.getRed(), co.getGreen(),
					co.getBlue());
			wcf_center.setBackground(Colour.ORANGE);
			// wcf_center.setShrinkToFit(true);
			WritableCellFormat wcf_left = new WritableCellFormat(normalFont);
			wcf_left.setBorder(Border.NONE, BorderLineStyle.THIN);
//			wcf_left.setVerticalAlignment(VerticalAlignment.CENTER);
			wcf_left.setAlignment(Alignment.LEFT);
			wcf_left.setWrap(false);

			sheet=setHeadSheet1(sheet, p_Titles,wcf_center);


			for (int row = 0; row < p_listContent.size(); row++) {
				Object[] dataRow = null;
				if (p_listContent.get(0) != null
						&& p_listContent.get(0).getClass().isArray()) {
					dataRow = (Object[]) (Object[]) p_listContent.get(row);
				} else {
					dataRow = (new Object[] { p_listContent.get(row) });
				}
				for (int col = 0; col < dataRow.length; col++) {
					if (dataRow[col] != null) {
						String listvalue = dataRow[col].toString();
						Label label = new Label(col, row + 1, listvalue,
								wcf_left);
						sheet.addCell(label);
					} else {
						Label label = new Label(col, row + 1, "", wcf_left);
						sheet.addCell(label);
					}
				}

			}
			workbook.write();
			workbook.close();
			os.flush();
			os.close();
		} catch (Exception e) {
			String message = "\u751F\u6210 Excel \u65F6\u51FA\u9519";
			if (log.isErrorEnabled()) {
				log.error(
						(new StringBuilder()).append("MXFRAMEWORK")
								.append(message).toString(), e);
			}
		}
	}

	private static WritableSheet setHeadSheet1(WritableSheet sheet,List p_Titles,WritableCellFormat wcf_center)
	{
		String[] titles1=(String[]) p_Titles.get(0);
		String[] titles2=(String[]) p_Titles.get(1);
		for (int i = 0; i < 2; i++) {
			for(int j=0;j<titles2.length;j++)
			{
				try {
					sheet.addCell(new Label(i, j, "", wcf_center));
				} catch (RowsExceededException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (WriteException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

				// 设置每列宽度
				sheet.setColumnView(i, 15);
			}
		}
		try {
			sheet.mergeCells(0, 0, 1, 0); //row,col
			sheet.mergeCells(0, 1, 1, 1);
			sheet.mergeCells(0, 2, 0, 3);

			sheet.mergeCells(0, 4, 0, 5);
			sheet.mergeCells(0, 6, 0, 7);
			sheet.mergeCells(0, 2, 0, 3);


		} catch (RowsExceededException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (WriteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

//		sheet.addMergedRegion(new Region(0,(short)0,1,(short)0));
//		((Sheet) sheet).addMergedRegion(new CellRangeAddress(0, 0, 1, 0));//单位名称


//		((Sheet) sheet).addMergedRegion(new CellRangeAddress(0, 1, 1, 1));//用车申请数


//		((Sheet) sheet).addMergedRegion(new CellRangeAddress(0, 2, 0, 3));//公务车

//		((Sheet) sheet).addMergedRegion(new CellRangeAddress(0, 4, 0, 5));//生产车
//
//		((Sheet) sheet).addMergedRegion(new CellRangeAddress(0, 6, 0, 7));//集体企业

		((Label) sheet.getCell(0,0)).setString("单位名称");

		((Label) sheet.getCell(0,1)).setString("用车申请数");

		((Label) sheet.getCell(0,2)).setString("公务车");

		((Label) sheet.getCell(0,4)).setString("生产车");

		((Label) sheet.getCell(0,6)).setString("集体企业");
//		sheet.

		for(int i=2;i<=7;i++)
		{
			((Label) sheet.getCell(1,i)).setString("数量");
			i++;
			((Label) sheet.getCell(1,i)).setString("派车单数");
		}

		return sheet;
	}

	private void setGB2312String(HSSFCell Cell, String Value) throws
			Exception {
		Cell.setCellValue(Value);
	}


	/**
	 * 获得浏览器类型
	 *
	 * @param request
	 *            HttpServletRequest
	 * @return String
	 */
	private static String getBrowser(HttpServletRequest request) {
		String userAgent = request.getHeader("USER-AGENT").toLowerCase();
		if (userAgent != null) {
			if (userAgent.indexOf("msie") >= 0) {
				return "IE";
			}
			if (userAgent.indexOf("firefox") >= 0) {
				return "FF";
			}
			if (userAgent.indexOf("safari") >= 0) {
				return "SF";
			}
		}
		return null;
	}

	/**
	 * 处理前台出入的参数
	 *
	 * @param request
	 *            HttpServletRequest
	 * @return String
	 */
	public static Map<String, String> dealWithParams(HttpServletRequest request) {
		Map<String, String> map = new HashMap<String, String>();
		String fileName = request.getParameter("fileName");
		String tableName = request.getParameter("tableName");
		String columnNames = request.getParameter("columnNames");
		String columnCaptions = request.getParameter("columnCaptions");
		String filter = request.getParameter("filter");
		if (StringUtils.isBlank(tableName)) {
			throw new RestClientException("未填写表名", new Exception());
		}
		if (StringUtils.isBlank(columnNames)) {
			columnNames = "*";
		}
		if (StringUtils.isBlank(columnCaptions)) {
			columnCaptions = columnNames;
		} else {
			try {
				columnCaptions = URLDecoder.decode(columnCaptions, "UTF-8");
			} catch (UnsupportedEncodingException e1) {
				e1.printStackTrace();
			}
		}
		if (filter != null) {
			try {
				filter = URLDecoder.decode(filter, "UTF-8");
			} catch (UnsupportedEncodingException e1) {
				e1.printStackTrace();
			}
		}
		if (fileName == null) {
			java.util.Date date = new java.util.Date();
			SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmssSSS");
			fileName = sdf.format(date);
		} else {
			try {
				fileName = URLDecoder.decode(fileName, "UTF-8");
			} catch (UnsupportedEncodingException e) {
				log.error(e);
			}
		}
		map.put("tableName", tableName);
		map.put("filter", filter);
		map.put("fileName", fileName);
		map.put("columnNames", columnNames);
		map.put("columnCaptions", columnCaptions);
		return map;
	}

	/**
	 * 统计分析运行模块导出调用此方法
	 */
	public static void exprtRunningReport(HttpServletRequest p_request, HttpServletResponse p_response, String p_fileName, String[] p_Titles, String[] p_columns, List<Map<String, Object>> p_listContent, String[] mergeColumnById,HSSFWorkbook workbook,HSSFSheet sheet,int rowNum) {
		exportExcel4(workbook,sheet,rowNum,p_request,p_response,p_fileName,p_columns,p_listContent,mergeColumnById);
	}

	/**
	 * 针对复杂表头导出
	 * @param p_request
	 * @param p_response
	 * @param p_fileName
	 * @param workbook
	 * @param p_columns
	 * @param p_listContent
	 * @param mergeColumnById
	 */
	public static final void exportExcel4(HSSFWorkbook workbook,HSSFSheet sheet,int beginRow, HttpServletRequest p_request, HttpServletResponse p_response, String p_fileName,String[] p_columns, List<Map<String, Object>> p_listContent, String[] mergeColumnById) {
		try{
			// 创建工作簿属性 普通的
			HSSFFont font1 = workbook.createFont();
			HSSFCellStyle style1 = workbook.createCellStyle();

			//合并单元格的计数
			Integer mergeColumnCount = 0;

			//遍历具体数据
			for(int index = 0; index < p_listContent.size(); index++){

				//是否与上一行相等
				Boolean mergeColumnFlagBoolean = false;

				/*与上一行ID相同时合并单元格*/
				//判断传了要合并的列且ID与上一行相同
				if(mergeColumnById != null && mergeColumnById.length > 0 && index > 0){
					String previousId = p_listContent.get(index-1).get("ID").toString();
					if(previousId != null && previousId.equals(p_listContent.get(index).get("ID").toString())){
						//与上一行相等
						mergeColumnFlagBoolean = true;
						//合并单元格计数增加
						mergeColumnCount++;
					}
				}

				/*添加数据内容*/
				Map<String, Object> data = p_listContent.get(index);
				//创建行
				HSSFRow dataRow = sheet.createRow(index + beginRow);
				//序号一列的值 1,2,3,4,5
				HSSFCell dataCell1 = dataRow.createCell(0);
				//设置样式
				dataCell1.setCellStyle(getColumnStyle2(font1, style1));
				//添加文本
				dataCell1.setCellValue(index+1);

				//设置行高
				dataRow.setHeight((short)(19.5 * 20));

				//遍历数据库列名
				for(int index2 = 0; index2 < p_columns.length; index2++){
					String column = p_columns[index2];
					if(mergeColumnFlagBoolean){//与上一行相等
						if(!ArrayUtils.contains(mergeColumnById, column)){//不合并的列
							/*新增单元格*/
							//创建单元格
							HSSFCell dataCell = dataRow.createCell(index2+1);
							//取数据内容
							String dataProperty = "";
							if(data.get(column)!=null){
								dataProperty = data.get(column).toString();
							}
							//设置样式
							dataCell.setCellStyle(getColumnStyle2(font1, style1));
							//当时数字类型时，设置单元格数据为数字类型
							if((StringUtils.isNotBlank(dataProperty))&&("java.math.BigDecimal").equals(data.get(column).getClass().getName())){
								dataCell.setCellValue( Double.parseDouble(dataProperty));
							}else{
								//添加文本
								dataCell.setCellValue(dataProperty);
							}

							//设置列宽
							Integer columnWidth;
							if (dataProperty.getBytes().length > 255) {
								columnWidth = 1 * 256 + 256 * 4;
							}else {
								columnWidth = dataProperty.getBytes().length * 256 + 256 * 4;
							}
//    	                    Integer columnWidth = dataProperty.getBytes().length * 256 + 256 * 4;
							if(sheet.getColumnWidth(index2+1) < columnWidth){
								sheet.setColumnWidth(index2+1, columnWidth);
							}
						}
					}else{//与上一行不相等
						if(mergeColumnCount > 0 && ArrayUtils.contains(mergeColumnById, column)){//合并前几行
							CellRangeAddress region = new CellRangeAddress(index - mergeColumnCount, index, index2+1, index2+1);
							sheet.addMergedRegion(region);
						}
						/*新增单元格*/
						//创建单元格
						HSSFCell dataCell = dataRow.createCell(index2+1);
						//取数据内容
						String dataProperty = new String();
						if(data != null){
							dataProperty = data.get(column) == null ? "":data.get(column).toString();
						}
						//设置样式
						dataCell.setCellStyle(getColumnStyle2(font1, style1));
						//当时数字类型时，设置单元格数据为数字类型
						if((StringUtils.isNotBlank(dataProperty))&&("java.math.BigDecimal").equals(data.get(column).getClass().getName())){
							dataCell.setCellValue( Double.parseDouble(dataProperty));
						}else{
							//添加文本
							dataCell.setCellValue(dataProperty);
						}
						//设置列宽
						Integer columnWidth;
						if (dataProperty.getBytes().length > 255) {
							columnWidth = 1 * 256 + 256 * 4;
						}else {
							columnWidth = dataProperty.getBytes().length * 256 + 256 * 4;
						}
//                        Integer columnWidth = dataProperty.getBytes().length * 256 + 256 * 4;
						if(sheet.getColumnWidth(index2+1) < columnWidth){
							sheet.setColumnWidth(index2+1, columnWidth);
						}
					}

					/*最后一行处理时合并掉计数器中的行*/
					if(index == p_listContent.size() - 1){
						if(mergeColumnCount > 0 && ArrayUtils.contains(mergeColumnById, column)){//合并前几行
							CellRangeAddress region = new CellRangeAddress(index - mergeColumnCount + 1, index + 1, index2+1, index2+1);
							sheet.addMergedRegion(region);
						}
					}

				}
				//与上一行Id不相等，且有合并计数时将合并计数重置
				if(mergeColumnCount > 0 && !mergeColumnFlagBoolean){
					mergeColumnCount = 0;
				}
			}

			/*输出*/
			//建立输出流
			OutputStream os = p_response.getOutputStream();
			//重置返回对象
			p_response.reset();
			//文件名编码
			String filenamedisplay = URLEncoder.encode(p_fileName, "UTF-8");
			if ("FF".equals(getBrowser(p_request))) {
				filenamedisplay = new String(p_fileName.getBytes("UTF-8"), "iso-8859-1");
			}
			//设置返回对象头信息
			p_response.addHeader("Content-Disposition", (new StringBuilder()).append("attachment; filename=").append(filenamedisplay).toString());
			p_response.addHeader("Content-Type", "application/vnd.ms-excel");
			workbook.write(os);
		}catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 多表头导出---标题样式
	 * @param font
	 * @param style
	 * @param palette
	 * @return
	 */
	public static final HSSFCellStyle getManyColumnHeaderStyle(HSSFFont font, HSSFCellStyle style,  HSSFPalette palette) {
		//
		font.setFontName("宋体");
		//字体白色
		//font.setColor(HSSFColor.WHITE.index);
		font.setFontHeightInPoints(Short.valueOf("16"));
//		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 设置加粗
		font.setBold(true);//设置加粗
		//
		style.setAlignment(HorizontalAlignment.CENTER);//水平居中
		style.setBorderTop(BorderStyle.THIN);//上边框
		style.setBorderBottom(BorderStyle.THIN);//下边框
		style.setBorderLeft(BorderStyle.THIN);//左边框
		style.setBorderRight(BorderStyle.THIN);//右边框

//		style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单元格下边框
//		style.setBorderTop(HSSFCellStyle.BORDER_THIN); // 设置单元格上边框
//		style.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 设置单元格上边框
//		style.setBorderRight(HSSFCellStyle.BORDER_THIN); // 设置单元格右边框
//		style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
		style.setVerticalAlignment(org.apache.poi.ss.usermodel.VerticalAlignment.CENTER);
		//设置绿色背景
//      HSSFPalette palette = workbook.getCustomPalette();
		//palette.setColorAtIndex(HSSFColor.GREEN.index, (byte)94, (byte)146, (byte)58);
		//style.setFillForegroundColor(HSSFColor.GREEN.index);
		//style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		//style_bt_no.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);//创建跨行居中格式
		style.setFont(font);
		return style;
	}

	public static final HSSFCellStyle getColumnStatTimeStyle(HSSFFont font, HSSFCellStyle style,  HSSFPalette palette) {

		//设置字体名字
		font.setFontName("宋体");
		//设置字体大小
		font.setFontHeightInPoints(Short.valueOf("12"));
//		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		font.setBold(true);//设置加粗
		//设置水平对齐的样式为居中对齐;
//		style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
		style.setAlignment(HorizontalAlignment.RIGHT);
		//设置垂直对齐的样式为居中对齐;
//		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		style.setVerticalAlignment(org.apache.poi.ss.usermodel.VerticalAlignment.CENTER);
		/*应用字体*/
		style.setFont(font);

		return style;
	}
	/**
	 * 文件头中除去标题行和统计时间行的 其他行数据样式 背景绿色
	 * @param font
	 * @param style
	 * @param palette
	 * @return
	 */
	public static final HSSFCellStyle getMutiColumnTitlesStyle(HSSFFont font, HSSFCellStyle style,  HSSFPalette palette) {
		/*设置字体*/
//        HSSFFont font = workbook.createFont();
		//设置字体名字
		font.setFontName("宋体");
		//设置字体大小
		font.setFontHeightInPoints(Short.valueOf("12"));
		//字体白色
		font.setColor(HSSFColor.HSSFColorPredefined.WHITE.getColor().getIndex());
		//字体加粗
//		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		font.setBold(true);//设置加粗

		/*设置样式*/
//        HSSFCellStyle style = workbook.createCellStyle();
		//设置水平对齐的样式为居中对齐;
//		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setAlignment(HorizontalAlignment.CENTER);
		//设置垂直对齐的样式为居中对齐;
//		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		style.setVerticalAlignment(org.apache.poi.ss.usermodel.VerticalAlignment.CENTER);
		//设置绿色背景
//        HSSFPalette palette = workbook.getCustomPalette();
//		palette.setColorAtIndex(HSSFColor.GREEN.index, (byte)94, (byte)146, (byte)58);
//		style.setFillForegroundColor(HSSFColor.GREEN.index);
//		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

		style.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 设置填充模式，模式为全部前景色
		style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREEN.getColor().getIndex());



//		style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单元格下边框
//		style.setBorderTop(HSSFCellStyle.BORDER_THIN); // 设置单元格上边框
//		style.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 设置单元格左边框
//		style.setBorderRight(HSSFCellStyle.BORDER_THIN); // 设置单元格右边框

		style.setAlignment(HorizontalAlignment.CENTER);//水平居中
		style.setBorderTop(BorderStyle.THIN);//上边框
		style.setBorderBottom(BorderStyle.THIN);//下边框
		style.setBorderLeft(BorderStyle.THIN);//左边框
		style.setBorderRight(BorderStyle.THIN);//右边框

		/*应用字体*/
		style.setFont(font);

		return style;
	}

	/**
	 * 在表格中设置数据的样式
	 */
	private static final HSSFCellStyle getColumnStyle2(HSSFFont font, HSSFCellStyle style) {
		// 设置单元格表单内容样式
		font.setFontName("宋体");
		font.setFontHeightInPoints(Short.valueOf("9"));
		//
//		style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
//		style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单元格下边框
//		style.setBorderTop(HSSFCellStyle.BORDER_THIN); // 设置单元格上边框
//		style.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 设置单元格上边框
//		style.setBorderRight(HSSFCellStyle.BORDER_THIN); // 设置单元格右边框

		style.setAlignment(HorizontalAlignment.CENTER);//水平居中
		style.setBorderTop(BorderStyle.THIN);//上边框
		style.setBorderBottom(BorderStyle.THIN);//下边框
		style.setBorderLeft(BorderStyle.THIN);//左边框
		style.setBorderRight(BorderStyle.THIN);//右边框

		style.setFillBackgroundColor(HSSFFont.COLOR_NORMAL);
		/*应用字体*/
		style.setFont(font);
		return style;
	}
}
